package com.kh.pajx.sop.dao.impl;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.hibernate.Session;
import org.hibernate.jdbc.Work;
import org.springframework.stereotype.Repository;

import com.kh.pajx.sop.base.BaseDaoImpl;
import com.kh.pajx.sop.dao.ISysPosDebugDao;
import com.kh.pajx.sop.domain.SysPosDebug;

@Repository
public class SysPosDebugDaoImpl extends BaseDaoImpl<SysPosDebug> implements
		ISysPosDebugDao {

	@Override
	public List<List<String>> getDebugMes(Map<String, String> params,
			int pageSize, int pageNum) {
		String sql = "";
		sql += " select s.STU_ID,s.STU_NAME,s.STU_NO,s.GRA_ID,s.CLS_ID,c.CARD_SN,c.CARD_RFID,"
				+ " d.POS_TIME,d.UPLOAD_TIME,d.STOPCODE,d.EQU_NO"
				+ " from SYS_POS_DEBUG d inner join HS_STU_CARD_INFO c on d.CARD_SN=c.CARD_SN or d.CARD_SN=c.CARD_RFID"
				+ " inner join HS_STUDENT_INFO s on c.STU_ID=s.STU_ID where s.SCL_ID='"
				+ params.get("sclId")
				+ "' ";
				
		if (StringUtils.isNotBlank(params.get("clsId"))) {
			sql += " and s.CLS_ID='" + params.get("clsId").trim() + "'";
		}
		if (StringUtils.isNotBlank(params.get("graId"))) {
			sql += " and s.GRA_ID='" + params.get("graId").trim() + "'";
		}
		if (StringUtils.isNotBlank(params.get("cardSn"))) {
			sql += " and c.card_sn='" + params.get("cardSn").trim()
					+ "' or c.card_rfid='" + params.get("cardSn").trim() + "'";
		}
		if (StringUtils.isNotBlank(params.get("startDate"))) {
			sql += " and d.POS_TIME>='"
					+ com.kh.pajx.sop.util.common.FormatDate
							.getMarryTime(params.get("startDate").trim())
					+ "000000" + "'";
		}
		if (StringUtils.isNotBlank(params.get("endDate"))) {
			sql += " AND d.POS_TIME<='"
					+ com.kh.pajx.sop.util.common.FormatDate
							.getMarryTime(params.get("endDate").trim())
					+ "235959" + "'";
		}
		if (StringUtils.isNotBlank(params.get("stuName"))) {
			sql += " and s.STU_NAME like '%" + params.get("stuName").trim()
					+ "%'";
		}
		if (StringUtils.isNotBlank(params.get("stuNo"))) {
			sql += " and s.STU_NO like '%" + params.get("stuNo").trim() + "%'";
		}
		String sql2 = "select * from ( select temp.*,ROWNUM rn from (" + sql + " order by d.POS_TIME desc ) temp where ROWNUM<="
				+ pageSize* pageNum+") where rn>="
				+ ((pageNum - 1) * pageSize + 1);
		 //System.out.println("基本信息:"+sql2);
		return this.getByJdbcSQL(sql2);
	}

	@Override
	public List<List<String>> getDicMes() {
		String sql = "select d.DIC_CODE,d.DIC_NAME from SYS_DIC d where d.DIC_TYPE_CODE ='1005'";
		// System.out.println("字典"+sql);
		return this.getByJdbcSQL(sql);
	}

	@Override
	public List<List<String>> getAllGrade(String sclId) {
		// TODO Auto-generated method stub
		String sql = " select g.GRA_ID,g.GRA_NAME,g.GRA_SHOW_NAME from HS_GRADE_INFO g where g.SCL_ID='"
				+ sclId + "'";
		// System.out.println("年级"+sql);
		return this.getByJdbcSQL(sql);
	}

	@Override
	public List<List<String>> getAllCls(String sclId) {
		String sql = " select c.CLS_ID,c.CLS_NAME,c.CLS_SHOW_NAME from HS_CLASS_INFO c where c.SCL_ID='"
				+ sclId + "'";
		// System.out.println("班级"+sql);
		return this.getByJdbcSQL(sql);
	}

	//含有分页
	@Override
	public List<List<String>> getRecordMes(Map<String, String> params,
			int pageSize, int pageNum) {
		String sql = " select s.STU_ID,s.STU_NAME,s.GRA_ID,s.CLS_ID,s.STU_NO,c.CARD_SN,"
				+ " c.CARD_RFID,r.EQU_NO,r.POS_TIME,r.UPLOAD_TIME "
				+ " from HS_POS_RECORD r inner join HS_STU_CARD_INFO c  on r.CARD_SN_OR_RFID=c.CARD_SN or r.CARD_SN_OR_RFID=c.CARD_RFID"
				+ " inner join HS_STUDENT_INFO s on c.STU_ID=s.STU_ID where s.SCL_ID='"
				+ params.get("sclId")
				+ "' and c.RELATION_STATUS_FLAG='1'";
				
		if (StringUtils.isNotBlank(params.get("clsId"))) {
			sql += " and s.CLS_ID='" + params.get("clsId").trim() + "'";
		}
		if (StringUtils.isNotBlank(params.get("graId"))) {
			sql += " and s.GRA_ID='" + params.get("graId").trim() + "'";
		}
		if (StringUtils.isNotBlank(params.get("startDate"))) {
			sql += " and r.POS_TIME>='"
					+ com.kh.pajx.sop.util.common.FormatDate
							.getMarryTime(params.get("startDate").trim())
					+ "000000" + "'";
		}
		if (StringUtils.isNotBlank(params.get("endDate"))) {
			sql += " and r.POS_TIME<='"
					+ com.kh.pajx.sop.util.common.FormatDate
							.getMarryTime(params.get("endDate").trim())
					+ "235959" + "'";
		}
		if (StringUtils.isNotBlank(params.get("equNo"))
				&& !params.get("equNo").equals("0")) {
			sql += " and r.EQU_NO='" + params.get("equNo").trim() + "'";
		}
		if (StringUtils.isNotBlank(params.get("stuName"))) {
			sql += " and s.STU_NAME like '%" + params.get("stuName").trim()
					+ "%'";
		}
		if (StringUtils.isNotBlank(params.get("stuNo"))) {
			sql += " and s.STU_NO like '%" + params.get("stuNo").trim() + "%'";
		}
		String sql2 = "select * from ( select temp.*,rownum rn from (" + sql + " order by r.POS_TIME desc ) temp where rownum<="
				+ pageSize* pageNum+")"+ " where rn>="
				+ ((pageNum - 1) * pageSize + 1);
		 //System.out.println("刷卡记录："+sql2);
		return this.getByJdbcSQL(sql2);
	}

	@Override
	public int recordCount(Map<String, String> params) {
		String sql = " select s.STU_ID,s.STU_NAME,s.GRA_ID,s.CLS_ID,s.STU_NO,c.CARD_SN,"
				+ " c.CARD_RFID,r.EQU_NO,r.POS_TIME,r.UPLOAD_TIME ,rownum rn"
				+ " from HS_POS_RECORD r inner join HS_STU_CARD_INFO c  on r.CARD_SN_OR_RFID=c.CARD_SN or r.CARD_SN_OR_RFID=c.CARD_RFID"
				+ " inner join HS_STUDENT_INFO s on c.STU_ID=s.STU_ID where s.SCL_ID='"
				+ params.get("sclId") + "'";
		if (StringUtils.isNotBlank(params.get("clsId"))) {
			sql += " and s.CLS_ID='" + params.get("clsId").trim() + "'";
		}
		if (StringUtils.isNotBlank(params.get("graId"))) {
			sql += " and s.GRA_ID='" + params.get("graId").trim() + "'";
		}
		if (StringUtils.isNotBlank(params.get("startDate"))) {
			sql += " and r.POS_TIME>='"
					+ com.kh.pajx.sop.util.common.FormatDate
							.getMarryTime(params.get("startDate").trim())
					+ "000000" + "'";
		}
		if (StringUtils.isNotBlank(params.get("endDate"))) {
			sql += " and r.POS_TIME<='"
					+ com.kh.pajx.sop.util.common.FormatDate
							.getMarryTime(params.get("endDate").trim())
					+ "235959" + "'";
		}
		if (StringUtils.isNotBlank(params.get("equNo"))
				&& !params.get("equNo").equals("0")) {
			sql += " and r.EQU_NO=" + params.get("equNo");
		}
		if (StringUtils.isNotBlank(params.get("stuName"))) {
			sql += " and s.STU_NAME like '%" + params.get("stuName").trim()
					+ "%'";
		}
		if (StringUtils.isNotBlank(params.get("stuNo"))) {
			sql += " and s.STU_NO like '%" + params.get("stuNo").trim() + "%'";
		}
		String sql2 = "select count(*) from (" + sql + ")";
		return this.getBySql(sql2);
	}

	@Override
	public int debugCount(Map<String, String> params) {
		String sql = " select s.STU_ID,s.STU_NAME,s.STU_NO,s.GRA_ID,s.CLS_ID,c.CARD_SN,c.CARD_RFID,"
				+ " d.POS_TIME,d.UPLOAD_TIME,d.STOPCODE,d.EQU_NO,ROWNUM rn"
				+ " from SYS_POS_DEBUG d inner join HS_STU_CARD_INFO c on d.CARD_SN=c.CARD_SN or d.CARD_SN=c.CARD_RFID"
				+ " inner join HS_STUDENT_INFO s on c.STU_ID=s.STU_ID where s.SCL_ID='"
				+ params.get("sclId") + "' ";
		if (StringUtils.isNotBlank(params.get("clsId"))) {
			sql += " and s.CLS_ID='" + params.get("clsId").trim() + "'";
		}
		if (StringUtils.isNotBlank(params.get("graId"))) {
			sql += " and s.GRA_ID='" + params.get("graId").trim() + "'";
		}
		if (StringUtils.isNotBlank(params.get("cardSn"))) {
			sql += " and c.card_sn='" + params.get("cardSn").trim()
					+ "' or c.card_rfid='" + params.get("cardSn").trim() + "'";
		}
		if (StringUtils.isNotBlank(params.get("startDate"))) {
			sql += " and d.POS_TIME>='"
					+ com.kh.pajx.sop.util.common.FormatDate
							.getMarryTime(params.get("startDate").trim())
					+ "000000" + "'";
		}
		if (StringUtils.isNotBlank(params.get("endDate"))) {
			sql += " and d.POS_TIME<='"
					+ com.kh.pajx.sop.util.common.FormatDate
							.getMarryTime(params.get("endDate").trim())
					+ "235959" + "'";
		}
		if (StringUtils.isNotBlank(params.get("stuName"))) {
			sql += " and s.STU_NAME like '%" + params.get("stuName").trim()
					+ "%'";
		}
		if (StringUtils.isNotBlank(params.get("stuNo"))) {
			sql += " and s.STU_NO like '%" + params.get("stuNo").trim() + "%'";
		}
		String sql2 = " select count(*) from (" + sql + ")";
		return this.getBySql(sql2);
	}

	public int getBySql(String sql) {
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		Session session = null;
		session = getSession().getSessionFactory().openSession();
		con = session.connection();
		int count = 0;
		try {
			pst = con.prepareStatement(sql);
			rs = pst.executeQuery();

			while (rs.next()) {
				count = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

			try {
				if (rs != null) {
					rs.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if (pst != null) {
					pst.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if (session != null) {
					session.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return count;
	}

	@Override
	public List<List<String>> getTeaDebugMes(Map<String, String> params,
			int pageSize, int pageNum) {
		// TODO Auto-generated method stub
		String sql = "select i.TEA_NAME,i.TEA_NO, t.CARD_SN,t.CARD_RFID,p.EQU_NO,"
				+ " p.POS_TIME,p.UPLOAD_TIME,p.STOPCODE "
				+ " from SYS_POS_DEBUG p inner join HS_TEA_CARD_INFO t on p.CARD_SN=t.CARD_SN or p.CARD_SN=t.CARD_RFID"
				+ " inner join HS_TEACHER_INFO i on t.TEA_ID=i.TEA_ID where i.scl_id='"
				+ params.get("sclId")
				+ "'";
		if (StringUtils.isNotBlank(params.get("startDate"))) {
			sql += " and p.POS_TIME>='"
					+ com.kh.pajx.sop.util.common.FormatDate
							.getMarryTime(params.get("startDate").trim())
					+ "000000" + "'";
		}
		if (StringUtils.isNotBlank(params.get("endDate"))) {
			sql += " and p.POS_TIME<='"
					+ com.kh.pajx.sop.util.common.FormatDate
							.getMarryTime(params.get("endDate").trim())
					+ "235959" + "'";
		}
		if (StringUtils.isNotBlank(params.get("cardSn"))) {
			sql += " and t.card_sn='" + params.get("cardSn").trim()
					+ "' or t.card_rfid='" + params.get("cardSn").trim() + "'";
		}
		if (StringUtils.isNotBlank(params.get("stuName"))) {
			sql += " and i.TEA_NAME like '%" + params.get("stuName").trim()
					+ "%'";
		}
		if (StringUtils.isNotBlank(params.get("stuNo"))) {
			sql += " and i.TEA_NO like '%" + params.get("stuNo").trim() + "%'";
		}
		String sql2 = "select * from (select temp.*,ROWNUM rn from (" + sql + " order by p.POS_TIME desc) temp  where rownum<="
				+ pageSize* pageNum+") where rn>="+ ((pageNum - 1) * pageSize + 1);
		 System.out.println(sql2);
		return this.getByJdbcSQL(sql2);
	}

	@Override
	public int teaDebugCount(Map<String, String> params) {
		// TODO Auto-generated method stub
		String sql = "select i.TEA_NAME,i.TEA_NO, t.CARD_SN,t.CARD_RFID,p.EQU_NO,"
				+ " p.POS_TIME,p.UPLOAD_TIME,p.STOPCODE "
				+ " from SYS_POS_DEBUG p inner join HS_TEA_CARD_INFO t on p.CARD_SN=t.CARD_SN or p.CARD_SN=t.CARD_RFID"
				+ " inner join HS_TEACHER_INFO i on t.TEA_ID=i.TEA_ID where i.scl_id='"
				+ params.get("sclId") + "'";
		if (StringUtils.isNotBlank(params.get("startDate"))) {
			sql += " and p.POS_TIME>='"
					+ com.kh.pajx.sop.util.common.FormatDate
							.getMarryTime(params.get("startDate").trim())
					+ "000000" + "'";
		}
		if (StringUtils.isNotBlank(params.get("endDate"))) {
			sql += " and p.POS_TIME<='"
					+ com.kh.pajx.sop.util.common.FormatDate
							.getMarryTime(params.get("endDate").trim())
					+ "235959" + "'";
		}
		if (StringUtils.isNotBlank(params.get("cardSn"))) {
			sql += " and t.card_sn='" + params.get("cardSn").trim()
					+ "' or t.card_rfid='" + params.get("cardSn").trim() + "'";
		}
		if (StringUtils.isNotBlank(params.get("stuName"))) {
			sql += " and i.TEA_NAME like '%" + params.get("stuName").trim()
					+ "%'";
		}
		if (StringUtils.isNotBlank(params.get("stuNo"))) {
			sql += " and i.TEA_NO like '%" + params.get("stuNo").trim() + "%'";
		}
		String sql2 = " select count(*) from (" + sql + ")";
		// System.out.println(sql2);
		return this.getBySql(sql2);
	}

	@Override
	public List<List<String>> getTeaRecordMes(Map<String, String> params,
			int pageSize, int pageNum) {
		// TODO Auto-generated method stub
		String sql = " select tea.TEA_NAME,tea.TEA_NO,t.CARD_SN,t.CARD_RFID,p.EQU_NO,p.POS_TIME,p.UPLOAD_TIME"
				+ " from hs_pos_record p inner join HS_TEA_CARD_INFO t on p.CARD_SN_OR_RFID=t.CARD_SN or p.CARD_SN_OR_RFID=t.CARD_SN"
				+ " inner join hs_teacher_info tea on t.TEA_ID=tea.TEA_ID where tea.scl_id='"
				+ params.get("sclId")
				+ "'";
		if (StringUtils.isNotBlank(params.get("startDate"))) {
			sql += " and p.POS_TIME>='"
					+ com.kh.pajx.sop.util.common.FormatDate
							.getMarryTime(params.get("startDate").trim())
					+ "000000" + "'";
		}
		if (StringUtils.isNotBlank(params.get("endDate"))) {
			sql += " and p.POS_TIME<='"
					+ com.kh.pajx.sop.util.common.FormatDate
							.getMarryTime(params.get("endDate").trim())
					+ "235959" + "'";
		}
		if (StringUtils.isNotBlank(params.get("equNo"))
				&& !params.get("equNo").equals("0")) {
			sql += " and p.EQU_NO='" + params.get("equNo").trim() + "'";
		}
		if (StringUtils.isNotBlank(params.get("stuName"))) {
			sql += " and tea.TEA_NAME like '%" + params.get("stuName").trim()
					+ "%'";
		}
		if (StringUtils.isNotBlank(params.get("stuNo"))) {
			sql += " and tea.TEA_NO like '%" + params.get("stuNo").trim()
					+ "%'";
		}
		if (StringUtils.isNotBlank(params.get("cardSn"))) {
			sql += " and t.card_sn='" + params.get("cardSn").trim()
					+ "' or t.card_rfid='" + params.get("cardSn").trim() + "'";
		}
		String sql2 = "select * from (select temp.*,ROWNUM rn from (" + sql + " order by p.POS_TIME desc ) temp where rownum<="
				+ pageSize* pageNum +") where rn>="
				+ ((pageNum - 1) * pageSize + 1);
		 System.out.println(sql2);
		return this.getByJdbcSQL(sql2);
	}

	@Override
	public int teaRecordCount(Map<String, String> params) {
		// TODO Auto-generated method stub
		String sql = " select tea.TEA_NAME,tea.TEA_NO,t.CARD_SN,t.CARD_RFID,p.EQU_NO,p.POS_TIME,p.UPLOAD_TIME,ROWNUM rn"
				+ " from hs_pos_record p inner join HS_TEA_CARD_INFO t on p.CARD_SN_OR_RFID=t.CARD_SN or p.CARD_SN_OR_RFID=t.CARD_SN"
				+ " inner join hs_teacher_info tea on t.TEA_ID=tea.TEA_ID where tea.scl_id='"
				+ params.get("sclId") + "'";
		if (StringUtils.isNotBlank(params.get("startDate"))) {
			sql += " and p.POS_TIME>='"
					+ com.kh.pajx.sop.util.common.FormatDate
							.getMarryTime(params.get("startDate").trim())
					+ "000000" + "'";
		}
		if (StringUtils.isNotBlank(params.get("endDate"))) {
			sql += " and p.POS_TIME<='"
					+ com.kh.pajx.sop.util.common.FormatDate
							.getMarryTime(params.get("endDate").trim())
					+ "235959" + "'";
		}
		if (StringUtils.isNotBlank(params.get("equNo"))
				&& !params.get("equNo").equals("0")) {
			sql += " and p.EQU_NO='" + params.get("equNo").trim() + "'";
		}
		if (StringUtils.isNotBlank(params.get("stuName"))) {
			sql += " and tea.TEA_NAME like '%" + params.get("stuName").trim()
					+ "%'";
		}
		if (StringUtils.isNotBlank(params.get("stuNo"))) {
			sql += " and tea.TEA_NO like '%" + params.get("stuNo").trim()
					+ "%'";
		}
		if (StringUtils.isNotBlank(params.get("cardSn"))) {
			sql += " and t.card_sn='" + params.get("cardSn").trim()
					+ "' or t.card_rfid='" + params.get("cardSn").trim() + "'";
		}
		String sql2 = " select count(*) from (" + sql + ")";
		// System.out.println(sql2);
		return this.getBySql(sql2);
	}

	@Override
	public List<List<String>> getCmppMess(Map<String, String> params,
			int pageSize, int pageNum) {
		String sql = "select s.stu_id,s.gra_id,s.cls_id,s.stu_name,"
				+ " s.stu_no,c.dest_terminal_id,c.read_time,c.msg_content"
				+ " from cmpp3_submit_history c left join (select scl.scl_id, e.accessnumber"
				+ " from boss_ecbiz e inner join hs_school_info scl on e.ecid = scl.ecid)"
				+ " t on c.src_id =t.accessnumber left join hs_student_info s on c.useruuid=s.stu_id where "
				+ " t.scl_id='" + params.get("sclId") + "' ";
		if (StringUtils.isNotBlank(params.get("clsId"))) {
			sql += " and s.CLS_ID='" + params.get("clsId").trim() + "'";
		}
		if (StringUtils.isNotBlank(params.get("graId"))) {
			sql += " and s.GRA_ID='" + params.get("graId").trim() + "'";
		}
		if (StringUtils.isNotBlank(params.get("phone"))) {
			sql += " and c.dest_terminal_id='" + params.get("phone").trim()
					+ "'";
		}
		if (StringUtils.isNotBlank(params.get("startDate"))) {
			sql += " and c.read_time>=to_date('"
					+ com.kh.pajx.sop.util.common.FormatDate
							.getMarryTime(params.get("startDate").trim())
					+ "000000" + "','YYYY-MM-DD HH24:MI:SS')";
		}
		if (StringUtils.isNotBlank(params.get("endDate"))) {
			sql += " AND c.read_time<=to_date('"
					+ com.kh.pajx.sop.util.common.FormatDate
							.getMarryTime(params.get("endDate").trim())
					+ "235959" + "','YYYY-MM-DD HH24:MI:SS')";
		}
		if (StringUtils.isNotBlank(params.get("stuName"))) {
			sql += " and s.STU_NAME like '%" + params.get("stuName").trim()
					+ "%'";
		}
		if (StringUtils.isNotBlank(params.get("stuNo"))) {
			sql += " and s.STU_NO like '%" + params.get("stuNo").trim() + "%'";
		}
		sql += " order by c.READ_TIME desc";
		String sql2 = "select * from ( select temp.*,ROWNUM rn from (" + sql + " )temp where rownum<="+ pageSize * pageNum+") where rn>="
				+ ((pageNum - 1) * pageSize + 1);
	        //System.out.println(sql2);
		return this.getByJdbcSQL(sql2);
	}

	@Override
	public int getCmppCounts(Map<String, String> params) {
		// TODO Auto-generated method stub
		String sql = "select s.stu_id,s.gra_id,s.cls_id,s.stu_name,"
				+ " s.stu_no,c.dest_terminal_id,c.read_time,c.msg_content,ROWNUM rn"
				+ " from cmpp3_submit_history c left join (select scl.scl_id, e.accessnumber"
				+ " from boss_ecbiz e inner join hs_school_info scl on e.ecid = scl.ecid)"
				+ " t on c.src_id =t.accessnumber left join hs_student_info s on c.useruuid=s.stu_id where "
				+ " t.scl_id='" + params.get("sclId") + "'";
		if (StringUtils.isNotBlank(params.get("clsId"))) {
			sql += " and s.CLS_ID='" + params.get("clsId").trim() + "'";
		}
		if (StringUtils.isNotBlank(params.get("graId"))) {
			sql += " and s.GRA_ID='" + params.get("graId").trim() + "'";
		}
		if (StringUtils.isNotBlank(params.get("phone"))) {
			sql += " and c.dest_terminal_id='" + params.get("phone").trim()
					+ "'";
		}
		if (StringUtils.isNotBlank(params.get("startDate"))) {
			sql += " and c.read_time>=to_date('"
					+ com.kh.pajx.sop.util.common.FormatDate
							.getMarryTime(params.get("startDate").trim())
					+ "000000" + "','YYYY-MM-DD HH24:MI:SS')";
		}
		if (StringUtils.isNotBlank(params.get("endDate"))) {
			sql += " AND c.read_time<=to_date('"
					+ com.kh.pajx.sop.util.common.FormatDate
							.getMarryTime(params.get("endDate").trim())
					+ "235959" + "','YYYY-MM-DD HH24:MI:SS')";
		}
		if (StringUtils.isNotBlank(params.get("stuName"))) {
			sql += " and s.STU_NAME like '%" + params.get("stuName").trim()
					+ "%'";
		}
		if (StringUtils.isNotBlank(params.get("stuNo"))) {
			sql += " and s.STU_NO like '%" + params.get("stuNo").trim() + "%'";
		}
		String sql2 = " select count(*) from (" + sql + ")";
		// System.out.println(sql2);
		return this.getBySql(sql2);
	}

	@Override
	public List<String> getTelMessge(final String cardSn) throws SQLException {
		final List<String> list = new ArrayList<String>();
		getSession().doWork(new Work() {
			public void execute(Connection connection) {
				try {
					CallableStatement call = connection
							.prepareCall("{call proc_tel_backnumber(?,?,?,?,?,?)}");
					call.setString(1, cardSn);
					call.registerOutParameter(2,
							oracle.jdbc.OracleTypes.VARCHAR);
					call.registerOutParameter(3,
							oracle.jdbc.OracleTypes.VARCHAR);
					call.registerOutParameter(4,
							oracle.jdbc.OracleTypes.VARCHAR);
					call.registerOutParameter(5,
							oracle.jdbc.OracleTypes.VARCHAR);
					call.registerOutParameter(6,
							oracle.jdbc.OracleTypes.VARCHAR);
					call.executeQuery();
					list.add(call.getString(2)); // valid_flag
					list.add(call.getString(3)); // family_num
					list.add(call.getString(4)); // family_msisdn
					list.add(call.getString(5)); // family_relation
					list.add(call.getString(6)); // message_num
				} catch (SQLException e) {
					e.printStackTrace();
					list.add("执行失败");
				}
			}
		});
		return list;

		/*
		 * Connection conn = null; Session session =
		 * this.getSession().getSessionFactory().openSession();
		 * CallableStatement cst = null; conn = session.connection();
		 * cst=conn.prepareCall("{call proc_tel_backnumber(?,?,?,?,?,?)}");
		 * cst.setString(1,cardSn); cst.registerOutParameter(2,
		 * oracle.jdbc.OracleTypes.VARCHAR); cst.registerOutParameter(3,
		 * oracle.jdbc.OracleTypes.VARCHAR); cst.registerOutParameter(4,
		 * oracle.jdbc.OracleTypes.VARCHAR); cst.registerOutParameter(5,
		 * oracle.jdbc.OracleTypes.VARCHAR); cst.registerOutParameter(6,
		 * oracle.jdbc.OracleTypes.VARCHAR); cst.execute(); String valid_flag =
		 * cst.getString(2); String family_num = cst.getString(3); String
		 * family_msisdn = cst.getString(4); String family_relation =
		 * cst.getString(5); String message_num = cst.getString(6); List<String>
		 * list = new ArrayList<String>(); list.add(valid_flag);
		 * list.add(family_num); list.add(family_msisdn);
		 * list.add(family_relation); list.add(message_num); return list;
		 */
	}

	@Override
	public String getCardSnsByCardNo(String cardNo) {
		String sql = "select card_sn from hs_stu_card_info sc where sc.card_no = '"
				+ cardNo + "'";
		String sql2 = "select card_sn  from hs_tea_card_info tc where tc.card_no = '"
				+ cardNo + "'";
		String casn = this.getStringBySql(sql);
		if (casn != null) {
			return casn;
		} else {
			return this.getStringBySql(sql2);
		}

	}

	public String getStringBySql(String sql) {
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		Session session = null;
		session = getSession().getSessionFactory().openSession();
		con = session.connection();
		String str = null;
		try {
			pst = con.prepareStatement(sql);
			rs = pst.executeQuery();

			while (rs.next()) {
				str = rs.getString(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

			try {
				if (rs != null) {
					rs.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if (pst != null) {
					pst.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if (session != null) {
					session.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return str;
	}

	@Override
	public void sclReportMonth() {
		Connection con = null;
		Session session = null;
		session = getSession().getSessionFactory().openSession();
		con = session.connection();
		CallableStatement cst = null;
		try {
			cst = con.prepareCall("{call PROC_BOSS_SCHOOL_MONTH}");
			cst.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			if(cst!=null){
				cst.close();
			}
			if (con != null) {
				con.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		if (session != null) {
			session.close();
		}
	}

}
